
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EBAS_SP_GEM_ADRESSE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EBAS_SP_GEM_ADRESSE]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [DBO].[EBAS_SP_GEM_ADRESSE] 
	@session varchar(255),
	@studGuid varchar(255),
	@attention varchar(255),
	@email varchar(255),
	@gade varchar(255),
	@postnr varchar(255),
	@telefax varchar(255),
	@telefon varchar(255),
	@adresseType varchar(255)
WITH ENCRYPTION
AS
	DECLARE 
		@rettighed int,
		@postnrGuid uniqueidentifier,
		@rows int,
		@adresseGuid uniqueidentifier,
		@adrTypeGuid uniqueidentifier
	
	SET NOCOUNT ON

	CREATE TABLE #temp ( rett int )
	INSERT INTO #temp (rett)
	EXEC EBAS.DBO.EBAS_SP_SIKKERHED_VERIFICER_SESSION @session	

	SET @rettighed = (SELECT IsNull(MAX(rett),0) FROM #temp)
	
	IF 8 < @rettighed 
	BEGIN
		SET @adrTypeGuid = (SELECT GUID FROM EBAS_ADRESSE_TYPE WHERE TYPE = @adresseType)
		SET @postnrGuid = (SELECT GUID FROM EBAS_POST_NR WHERE POST_NR = @postnr)
		SET @rows = (	SELECT COUNT(*) FROM EBAS_ADRESSE 
				WHERE ADRESSE_TYPE_GUID = @adrTypeGuid
				AND EJER_GUID = @studGuid )
		IF 0 = @rows
		BEGIN
			SET @adresseGuid = NEWID();
			INSERT INTO EBAS_ADRESSE 
				SELECT 	@adresseGuid, @attention, @gade, @postnrGuid, @telefon, @telefax, 
					@email, @studguid, @adrTypeGuid			
		END
		ELSE
			UPDATE EBAS_ADRESSE SET GADE = @gade, TELEFON = @telefon, 
			TELEFAX = @telefax, E_MAIL = @email, POST_NR_GUID = @postnrGuid, ATTENTION = @attention
			WHERE ADRESSE_TYPE_GUID = @adrTypeGuid
			AND EJER_GUID = @studGuid
	
		SELECT A.GUID AS GUID, A.ATTENTION AS ATTENTION, A.GADE AS GADE, POST_NR, [BY], TELEFON, TELEFAX, E_MAIL, TYPE
		FROM EBAS_ADRESSE A, EBAS_ADRESSE_TYPE T, EBAS_POST_NR N
		WHERE A.ADRESSE_TYPE_GUID = T.GUID
		AND A.POST_NR_GUID = N.GUID
		AND A.EJER_GUID = @studGuid
	END
	ELSE
		SELECT A.GUID AS GUID, A.ATTENTION AS ATTENTION, A.GADE AS GADE, POST_NR, [BY], TELEFON, TELEFAX, E_MAIL, TYPE
		FROM EBAS_ADRESSE A, EBAS_ADRESSE_TYPE T, EBAS_POST_NR N
		WHERE A.ADRESSE_TYPE_GUID = T.GUID
		AND A.POST_NR_GUID = N.GUID
		AND A.GUID IS NULL
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
